﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MDV.Entities;
using System.Data.SqlClient;
using System.Data;


namespace MDV.DataAccess
{
    class DataObject
    {
        public string check = "At DataAccess";
        private DataTable dt;
        private SqlDataAdapter da = new SqlDataAdapter();
        private SqlConnection con = null;
        private SqlCommand command;

        public void loadData()
        {
            try
            {
                string conStr = "Server=localhost; Integrated Security=SSPI;database=Medicines";
                con = new SqlConnection(conStr);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }

            finally
            {
                if (con != null)
                    Console.WriteLine("Ket noi thanh cong");
                    //con.Close();
            }
        }

        public DataTable ShowAllData(int radioIndex)
        {
            loadData();

            dt = new DataTable("tblMedicines");

            try{
                command = new SqlCommand();
                command.Connection = con;
                command.CommandType = CommandType.Text;
                if (radioIndex == 0){
                    check = radioIndex.ToString() + "123";
                    command.CommandText = @"Select [Medicine Name], [Medicine ID] FROM Medicines";
                }
                else{
                    check = radioIndex.ToString() + "123";
                    command.CommandText = @"Select DISTINCT [Component Name], [Medicine ID] FROM Component";
                }
                da.SelectCommand = command;
            }catch(Exception ex){
                check = ex.ToString();
            }
            finally{
                da.Fill(dt);
                con.Close();
            }
            return dt;
        }

        public void UpdateData(string query) {
            con.Open();
            try
            {
                command = new SqlCommand();
                command.Connection = con;
                command.CommandType = CommandType.Text;
                command.CommandText =  query;
                command.ExecuteNonQuery();
               // check = query; //check cau lenh query              
            }
            catch (Exception ex)
            {
                check = ex.ToString();
            }
            finally
            {
                check = "Update thanh cong";
                con.Close();
            }
        }

        public DataTable Search(string strSearch,int index) {
            string que, tblname, column;
            string condition = strSearch;
            string query = "";
            que = "";
            string dst; //DISTINCT or not
            loadData();
            var dtAfterSearch = new DataTable();
            da = new SqlDataAdapter();

            if (index == 0)
            {
                que = strSearch;
                dst = null;
                tblname = "Medicines";
                column = "[Medicine Name], [Medicine ID]";
                query = "Select " + dst + column + " from " + tblname + " Where " + column + " Like N'%" + que +
                        "%' ";
            }
            else
            {
                dst = "DISTINCT";
                tblname = "Component";
                column = "[Component Name],[Medicine ID]";

                int i = condition.IndexOf(";");

                if (i < 0)
                {
                    //MessageBox.Show(i.ToString());
                    que = condition;
                    query = "Select " + dst + column + " from " + tblname + " Where " + column + " Like N'%" +
                            que + "%' ";
                }

                if (i > 0)
                {
                    string orque = null;

                    query = "Select " + dst + column + " from " + tblname + " Where ";
                    while (i > 0)
                    {


                        que = condition.Substring(0, i);
                        condition = condition.Remove(0, i + 1);

                        query += orque + column + " Like N'" + que + "%'";
                        orque = " OR ";
                        i = condition.IndexOf(";");

                        if (i < 0) que = condition;

                    }
                    query += orque + column + " Like N'" + que + "%'";
                }
                

            }
            var command = new SqlCommand();
            command.Connection = con;
            command.CommandType = CommandType.Text;
            command.CommandText = query;
            da.SelectCommand = command;
            da.Fill(dtAfterSearch);
            con.Close();
            return dtAfterSearch;
        }

        public DataTable RichSearchComponent(string strSearch) 
        {
            DataTable dtRich = new DataTable();
            SqlDataAdapter daRich = new SqlDataAdapter();
            string condition = strSearch;
            string column = " [Component Name]";
            string query=null;
            string que = null;
            int i = condition.IndexOf(";");
           
                string orque = null;

                query = "SELECT [Medicine Name], [Component Name],[Dose], [Medicine ID] " +
                        "FROM Medicines JOIN Component "+
                        "ON Medicines.[Medicine ID]=Component.[Medicine ID] "+
                        "WHERE ";// +column + " like '" + es + "%'";
                while (i > 0)
                {


                    que = condition.Substring(0, i);
                    condition = condition.Remove(0, i + 1);

                    query += orque + column + " Like '" + que + "%'";
                    orque = " OR ";
                    i = condition.IndexOf(";");

                    if (i < 0) que = condition;

                }
                query += orque + column + " Like '" + que + "%'";
           
            SqlCommand command = new SqlCommand();
            command.Connection = con;
            command.CommandType = CommandType.Text;
            command.CommandText = query;
            da.SelectCommand = command;
            da.Fill(dtRich);
            con.Close();
            return dtRich;
            
        }

        public DataTable Disease()
        {
            loadData();
            DataTable dataTree = new DataTable();
            try
            {
                
                command = new SqlCommand();
                command.Connection = con;
                command.CommandType = CommandType.Text;
                command.CommandText = @"Select DISTINCT [Type],[Disease],[Content] FROM Disease";
                da.SelectCommand = command;
            }
            catch (Exception ex) {
              check = ex.ToString();
            }
            finally
            {
                da.Fill(dataTree);
                con.Close();
            }
            return dataTree;
        }
    }
}
